﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using SSN.Data.Database;
using SSN.Objects;
using SSN.Objects.DBPersist;
using System.Text;

using SSN.DotNet;
using System.Data.Common;
using System.Data.SqlClient;

namespace SSN.Utilities.Database
{
    public static class DbManager
    {
        #region static references to databases
        /// <summary>
        /// Returns a reference to BOSS databse.
        /// </summary>
        public static SSNDB BossDb
        {
            get
            {
                return DBFactory.GetDB( SSNDBType.BossSQL );
            }
        }

        /// <summary>
        /// Returns a reference to BOSS databse.
        /// </summary>
        public static SSNDB AccountsDb
        {
            get
            {
                return DBFactory.GetDB( SSNDBType.BossSQL );
            }
        }

        /// <summary>
        /// Returns a reference to BOSS databse.
        /// </summary>
        public static SSNDB RepDb
        {
            get
            {
                return DBFactory.GetDB( SSNDBType.BossSQL );
            }
        }

        /// <summary>
        /// Returns a reference to commissions database.
        /// </summary>
        public static SSNDB CommissionsDB
        {
            get
            {
                return DBFactory.GetDB( "dbCommissions" );
            }
        }

        /// <summary>
        /// Returns a reference to the Albridge database.
        /// </summary>
        public static SSNDB AlbridgeDB
        {
            get
            {
                return DBFactory.GetDB( "dbAlbridge" );
            }
        }

        /// <summary>
        /// Returns a reference to the tradereview database.
        /// </summary>
        public const string TradeReviewDBName = "DbTradeReview";
        public static SSNDB TradeReviewDb
        {
            get
            {
                return DBFactory.GetDB( TradeReviewDBName );
            }
        }

        /// <summary>
        /// Returns a reference to the RevenueCenter database.
        /// </summary>
        public const string RevenueCenterDbName = "DbRevenueCenterBridge";
        public static SSNDB RevenueCenterDb
        {
            get
            {
                return DBFactory.GetDB( RevenueCenterDbName );
            }
        }
        #endregion

        #region database helper functions
        /// <summary>
        /// Returns a database from the sql statement.
        /// </summary>
        /// <param name="db">The database to query.</param>
        /// <param name="sql">The query string.</param>
        /// <returns></returns>
        public static DataTable GetTable( this SSNDB db, string sql )
        {
            try
            {
                var dt = new DataTable( );
                dt.Load( db.QueryFast( sql ) );
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                db.Close( );
            }
        }

        /// <summary>
        /// Runs the query and returns a list of objects as a result of the query of type T.
        /// </summary>
        /// <typeparam name="T">Type of DBPersistedObject</typeparam>
        /// <param name="db">The SSNDB to query. Suggest to use DbManager.&gt;DataBaseType&lt;</param>
        /// <param name="ctx">Security context.</param>
        /// <param name="sql">SQL statement.</param>
        /// <returns>List of DBPersistedObjects as a result of the query.</returns>
        public static List<T> CreateListFromQuery<T>( this SSNDB db, SSNSecurityContext ctx, string sql ) where T : SSNDataBasePersistedObject
        {
            using ( DataTable dt = new DataTable( ) )
            {
                dt.Load( db.QueryFast( sql ) );
                db.Close( );

                if ( dt == null ) return null;

                List<T> lt = new List<T>( );
                foreach ( DataRow dr in dt.Rows )
                {
                    T obj = Activator.CreateInstance<T>( );
                    if ( obj.Load( ctx, dr ) ) lt.Add( obj );
                }
                return lt;
            }
        }

	   /// <summary>
	   /// Runs the query and returns a list of objects as a result of the query of type T.
	   /// </summary>
	   /// <typeparam name="T">Type of DBPersistedObject</typeparam>
	   /// <param name="db">The SSNDB to query. Suggest to use DbManager.&gt;DataBaseType&lt;</param>
	   /// <param name="ctx">Security context.</param>
	   /// <param name="command">The DbCommand containing the SQL to run.</param>
	   /// <returns>List of DBPersistedObjects as a result of the query.</returns>
	   public static List<T> CreateListFromQuery<T>(this SSNDB db,SSNSecurityContext ctx,DbCommand command) where T:SSNDataBasePersistedObject {
		   using(DataTable dt = new DataTable()) {
			   dt.Load(db.QueryFast(command));
			   db.Close();

			   if(dt == null) return null;

			   List<T> lt = new List<T>();
			   foreach(DataRow dr in dt.Rows) {
				   T obj = Activator.CreateInstance<T>();
				   if(obj.Load(ctx,dr)) lt.Add(obj);
			   }
			   return lt;
		   }
	   }

        /// <summary>
        /// Runs the query as a format string and returns a list of objects as a result of the query of type T.
        /// </summary>
        /// <typeparam name="T">Type of DBPersistedObject</typeparam>
        /// <param name="db">The SSNDB to query. Suggest to use DbManager.&gt;DataBaseType&lt;</param>
        /// <param name="ctx">Security context.</param>
        /// <param name="sql">SQL statement as a format string.</param>
        /// <returns>List of DBPersistedObjects as a result of the query.</returns>
        public static List<T> CreateListFromQueryFormat<T>(this SSNDB db, SSNSecurityContext ctx, string sqlFormat, params object[] objects) where T : SSNDataBasePersistedObject
        {
            return db.CreateListFromQuery<T>(ctx, string.Format(sqlFormat, objects));
        }

        /// <summary>
        /// Runs the query and returns a list of objects as a result of the query of type T.
        /// </summary>
        /// <typeparam name="T">Type of DBPersistedObject</typeparam>
        /// <param name="dbt">The database type to query.</param>
        /// <param name="ctx">Security context.</param>
        /// <param name="sql">SQL statement.</param>
        /// <returns>List of DBPersistedObjects as a result of the query.</returns>
        public static List<T> CreateListFromQuery<T>( this SSNDBType dbt, SSNSecurityContext ctx, string sql ) where T : SSNDataBasePersistedObject
        {
            return DBFactory.GetDB( dbt ).CreateListFromQuery<T>( ctx, sql );
        }

	   /// <summary>
	   /// Runs the query and returns a list of objects as a result of the query of type T.
	   /// </summary>
	   /// <typeparam name="T">Type of DBPersistedObject</typeparam>
	   /// <param name="dbt">The database type to query.</param>
	   /// <param name="ctx">Security context.</param>
	   /// <param name="command">The DbCommand containing the SQL to run.</param>
	   /// <returns>List of DBPersistedObjects as a result of the query.</returns>
	   public static List<T> CreateListFromQuery<T>(this SSNDBType dbt,SSNSecurityContext ctx,DbCommand command) where T:SSNDataBasePersistedObject {
		   return DBFactory.GetDB(dbt).CreateListFromQuery<T>(ctx,command);
	   }


        /// <summary>
        /// Runs the query as a format string and returns a list of objects as a result of the query of type T.
        /// </summary>
        /// <typeparam name="T">Type of DBPersistedObject</typeparam>
        /// <param name="db">The SSNDB to query. Suggest to use DbManager.&gt;DataBaseType&lt;</param>
        /// <param name="ctx">Security context.</param>
        /// <param name="sqlFormat">SQL statement as a format string.</param>
        /// <returns>List of DBPersistedObjects as a result of the query.</returns>
        public static List<T> CreateListFromQueryFormat<T>(this SSNDBType dbt, SSNSecurityContext ctx, string sqlFormat, params object[] objects) where T : SSNDataBasePersistedObject
        {
            return dbt.CreateListFromQuery<T>(ctx, string.Format(sqlFormat, objects));
        }

        /// <summary>
        /// Runs the query and returns a list of objects as a result of the query of type T.
        /// </summary>
        /// <typeparam name="T">Type of DBPersistedObject</typeparam>
        /// <param name="db">The SSNDB to query. Suggest to use DbManager.&gt;DataBaseType&lt;</param>
        /// <param name="ctx">Security context.</param>
        /// <param name="sql">SQL statement.</param>
        /// <param name="timeOutInSeconds">Amount of time, in seconds, to set the timeout of the query.</param>
        /// <returns>List of DBPersistedObjects as a result of the query.</returns>
        public static List<T> CreateListFromQuery<T>( this SSNDB db, SSNSecurityContext ctx, string sql, int timeOutInSeconds ) where T : SSNDataBasePersistedObject, new( )
        {
            DataTable dt = new DataTable( );
            DbCommand cmd = new SqlCommand( sql );
            cmd.Connection = db.Connection;
            cmd.Connection.Open( );
            cmd.CommandTimeout = timeOutInSeconds;
            dt = db.Query( cmd ).Tables[ 0 ];
            db.Close( );

            if ( dt == null ) return null;

            List<T> lt = new List<T>( );
            foreach ( DataRow dr in dt.Rows )
            {
                T obj = new T( );
                if ( obj.Load( ctx, dr ) ) lt.Add( obj );
            }

            return lt;
        }

        /// <summary>
        /// Runs the query and returns a list of objects that implement ILoadable as a result of the query of type T.
        /// </summary>
        /// <typeparam name="T">ILoadable object</typeparam>
        /// <param name="db">The SSNDB to query. Suggest to use DbManager.&gt;DataBaseType&lt;</param>
        /// <param name="ctx">Security context.</param>
        /// <param name="sql">SQL statement.</param>
        /// <param name="timeOutInSeconds">Amount of time, in seconds, to set the timeout of the query.</param>
        /// <returns>List of DBPersistedObjects as a result of the query.</returns>
        public static List<T> CreateLoadableListFromQuery<T>( this SSNDB db, string sql ) where T : IDbLoadable, new( )
        {
            using ( DataTable dt = new DataTable( ) )
            {
                dt.Load( db.QueryFast( sql ) );
                db.Close( );

                if ( dt == null ) return null;

                List<T> lt = new List<T>( );
                foreach ( DataRow dr in dt.Rows )
                {
                    T obj = new T( );
                    if ( obj.Load( dr ) ) lt.Add( obj );
                }
                return lt;
            }
        }

        /// <summary>
        /// Runs the query and returns a list of objects that implement ILoadable as a result of the query of type T.
        /// </summary>
        /// <typeparam name="T">ILoadable object</typeparam>
        /// <param name="db">The database type to query.</param>
        /// <param name="ctx">Security context.</param>
        /// <param name="sql">SQL statement.</param>
        /// <param name="timeOutInSeconds">Amount of time, in seconds, to set the timeout of the query.</param>
        /// <returns>List of DBPersistedObjects as a result of the query.</returns>
        public static List<T> CreateLoadableListFromQuery<T>( this SSNDBType dbt, string sql ) where T : IDbLoadable, new( )
        {
            return DBFactory.GetDB( dbt ).CreateLoadableListFromQuery<T>( sql );
        }

	   /// <summary>
	   /// Returns a list of double values from a sql query.
	   /// </summary>
	   /// <param name="dbt">The database to query.</param>
	   /// <param name="query">the SQL query.</param>
	   /// <returns>A list of objects resulting from the query.</returns>
	   public static List<T> CreatePrimitiveList<T>(this SSNDBType dbt,string query) {
		   return DBFactory.GetDB(dbt).CreatePrimitiveList<T>(query);
	   }

        /// <summary>
        /// Returns a list of double values from a sql query.
        /// </summary>
        /// <param name="db">The database to query.</param>
        /// <param name="query">the SQL query.</param>
        /// <returns>A list of objects resulting from the query.</returns>
        public static List<T> CreatePrimitiveList<T>( this SSNDB db, string query )
        {
            using ( DataTable dt = new DataTable( ) )
            {
                dt.Load( db.QueryFast( query ) );
                db.Close( );

                if ( dt == null ) return null;

                var lt = new List<object>( );
                foreach ( DataRow dr in dt.Rows )
                {
                    object t = dr[ 0 ];
                    if ( t is int )
                    {
                        lt.Add( Convert.ToInt32( t ) );
                    }
                    else if ( t is double )
                    {
                        lt.Add( Convert.ToDouble( t ) );
                    }
                    else if ( t is DateTime )
                    {
                        lt.Add( Convert.ToDateTime( t ) );
                    }
                    else
                    {
                        lt.Add( t.ToString( ) );
                    }
                }

                return lt.CastAs<T>( );
            }
        }

	    /// <summary>
	    /// Creates a dictionary from the results of the given query. If the query returns more than two columns or
	    /// the generic types given do not match the data types of the returned columns, will return null.
	    /// </summary>
	    /// <typeparam name="K">The data type of the first returned column. Becomes the dictionary keys.</typeparam>
	    /// <typeparam name="V">The data type of the second returned column. Becomes the dictionary values.</typeparam>
	    /// <param name="databaseType">The database type to run the query on.</param>
	    /// <param name="ctx">The current user's security context.</param>
	    /// <param name="sql">The query the results of which will be transformed into the dictionary.</param>
	    /// <returns>A dictionary containing the results of the given query.</returns>
	   public static Dictionary<K,V> CreateDictionaryFromQuery<K,V>(this SSNDBType databaseType,SSNSecurityContext ctx,string sql) {
		   return DBFactory.GetDB(databaseType).CreateDictionaryFromQuery<K,V>(ctx,sql);
	   }

	   /// <summary>
	   /// Creates a dictionary from the results of the given query. If the query returns more than two columns or
	   /// the generic types given do not match the data types of the returned columns, will return null.
	   /// </summary>
	   /// <typeparam name="K">The data type of the first returned column. Becomes the dictionary keys.</typeparam>
	   /// <typeparam name="V">The data type of the second returned column. Becomes the dictionary values.</typeparam>
	   /// <param name="databaseType">The database to run the query on.</param>
	   /// <param name="ctx">The current user's security context.</param>
	   /// <param name="sql">The query the results of which will be transformed into the dictionary.</param>
	   /// <returns>A dictionary containing the results of the given query.</returns>
	   public static Dictionary<K,V> CreateDictionaryFromQuery<K,V>(this SSNDB database,SSNSecurityContext ctx,string sql) {
		   using(DataTable table = new DataTable()) {
			   table.Load(database.QueryFast(sql));
			   database.Close();

			   if(table == null||table.Columns.Count!=2)
				   return null;

			   if(table.Columns[0].DataType!=typeof(K)||table.Columns[1].DataType!=typeof(V))
				   return null;

			   Dictionary<K,V> dict=new Dictionary<K,V>();
			   foreach(DataRow dr in table.Rows)
				   dict[(K)dr[0]]=(V)dr[1];
			   return dict;
		   }
	   }


        /// <summary>
        /// Executes the command from a string format and does sql sanitation on the objects provided.
        /// </summary>
        /// <param name="dbType">Type of database</param>
        /// <param name="sqlFormatString">Format string represneting a SQL command.</param>
        /// <param name="objects">Any number of objects.</param>
        /// <returns>Scalar value resulting from the command.</returns>
        public static int ExecuteCommandFormat(this SSNDB db, string sqlFormatString, params object[] objects)
        {
            for (int i = 0; i < objects.Length; i++)
            {
                objects[i] = objects[i].ToString().Replace("'", "''");
            }

            return db.ExecuteCommand(string.Format(sqlFormatString, objects));
        }

        /// <summary>
        /// Executs a command from a DbCommand.
        /// </summary>
        /// <param name="dbType">Type of database.</param>
        /// <param name="command">DbCommand representing a sql statement.</param>
        /// <returns>Scalar value resulting from the command.</returns>
        public static int ExecuteCommand( this SSNDBType dbType, DbCommand command )
        {
            return DBFactory.GetDB( dbType ).ExecuteCommand( command );
        }

        /// <summary>
        /// Executes the command from a QueryBuilder.
        /// </summary>
        /// <param name="dbType">Type of database.</param>
        ///<param name="builder">Object  that builds a sql statement.</param>
        /// <returns>Scalar value resulting from the command.</returns>
        public static int ExecuteCommand( this SSNDBType dbType, QueryBuilder builder )
        {
            return DBFactory.GetDB( dbType ).ExecuteCommand( builder );
        }

        /// <summary>
        /// Executes the command from a sql string.
        /// </summary>
        /// <param name="dbType">Type of database.</param>
        /// <param name="sqlString">String representing a sql command.</param>
        /// <returns>Scalar value resulting from the command.</returns>
        public static int ExecuteCommand( this SSNDBType dbType, string sqlString )
        {
            return DBFactory.GetDB( dbType ).ExecuteCommand( sqlString );
        }

        /// <summary>
        /// Executes the command from a string format and does sql sanitation on the objects provided.
        /// </summary>
        /// <param name="dbType">Type of database</param>
        /// <param name="sqlFormatString">Format string represneting a SQL command.</param>
        /// <param name="objects">Any number of objects.</param>
        /// <returns>Scalar value resulting from the command.</returns>
        public static int ExecuteCommandFormat(this SSNDBType dbType, string sqlFormatString, params object[] objects)
        {
            for (int i = 0; i < objects.Length; i++)
            {
                objects[i] = objects[i].ToString().Replace("'", "''");
            }
            return DBFactory.GetDB(dbType).ExecuteCommand(string.Format(sqlFormatString, objects));
        }

        /// <summary>
        /// Formats the collection for listing in SQL.
        /// </summary>
        /// <typeparam name="T">Type of object.</typeparam>
        /// <param name="list">IEnumerable list of objects.</param>
        /// <param name="quoted">If true, item is surrounded by single quotes. If false, it is not.</param>
        /// <returns>The list of items as a sql ready string.</returns>
        public static string JoinToSqlString<T>( this IEnumerable<T> list, bool quoted = true )
        {
            StringBuilder sb = new StringBuilder( );

            foreach ( T t in list )
            {
                sb.AppendFormatWithFirst( "{1}{0}{1}", ",{1}{0}{1}", t, quoted ? "'" : "" );
            }

            return sb.ToString( );
        }

        /// <summary>
        /// Returns the scalar value of the sql command.
        /// </summary>
        /// <param name="db">Database to operate on.</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteScaler( this SSNDB db, string sql )
        {
            db.Open( );
            return db.ExecuteCommand( sql );
        }
        #endregion
    }
}
